Lightweigth data-interchange format

Motivations
- I'd like to receive data, not HTML content, from the server. For example, just data, not table code, for the search results in TRUQA. Any formal data format with AJAX?

Let's study JSON.
- Read all in JSON - Introduction.
  - What is JSON data in JavaScript?
  - For what do you use JSON?
  - Can a JSON file be a target URL for AJAX?
  - Is the JSON format syntactically identical to the code for creating JS objects?
  - What function is used to parse a JSON string into a JavaScript object?
  - Trial 1: Let's convert the object for 'ListQuestions' to a string, and the string to an object.
    <script> var query = {page: 'MainPage', command: 'ListQuestions'}; var str = ???(query); // convert the above object to a JSON string ???(str); // display the string using an alert window // ... // Let's assume a JSON str is sent from the server var obj = ???(str); // convert the JSON string to an object var s = ''; for (????) // find all the properties and their values in the object s += ????; // and attach them alert(s); </script>
- Read all in JSON Syntax.
  - Can you use single quotes? No.
  - List the 6 types of JSON values
  - What is the extension of JSON files?
  - What is the MIME type for JSON text?
- Read all in JSON How To.
  - In the examples, do you think employees[0]['firstName'] + " " + employees[0].lastName; will work? This idea can be used in many interesting ways.
- Read all in JSON Http Request.
  - Read the first example carefully.
  - What is XMLHttpRequest?
  - What kind of bracket is used for JSON arrays?
  - What kind of data is stored in 'myTutorials.txt'?
  - Can you move the AJAX routine into an external JavaScript file and include the file?
  - Trial 2: Let's get the list of questions from TRUQA, and display it in a table.
    <div id='tr2-result-pane'>Result pane</div> <button type='button' id='tr2-list-questions'>List Questions</button> <script> ???? // when the above button is clicked, call the next function function tr2_list_questions() { var url = 'truqa_list_questions_ajax.php'; // test program for 'ListQuestions' from TRUQA var query = {page: 'MainPage', command: 'ListQuestions'}; // jQuery post ????(???, ???, function(data) { alert(data); // just to see the format of the data var rows = ????(data); // convert a JSON string to an object // the object will be a linear array of associative arrays var t = '<table>'; for (var i = 0; i < ????; i++) { // for each row t += '<tr>'; for (????) // for each property t += '<td>' + ???? + '</td>'; // the property value, not the property name t += '</tr>'; } t += '</table>'; ????; // display the table into <div> of id='tr2-result-pane' }); } </script>
  - Trial 2.5: Can you add table headers with property names in the above Trial 2?
  - Trial 2.6: Can you attach delete buttons in each row in the table in the above Trial 2? How to obtain the Id value when the delete button is clicked? It can be used when the DeleteQuestion command is submitted.
    <div id='tr2-6-result-pane'>Result pane</div> <button type='button' id='tr2-6-list-questions'>List Questions</button> <script> ???? function tr2_6_list_questions() { var url = "truqa_list_questions_ajax.php"; // test program for 'ListQuestions' from TRUQA var query = {page: "MainPage", command: "ListQuestions"}; // jQuery post $.post(url, query, function(data) { var rows = ????(data); // convert a JSON string to an object // the object will be a linear array of associative arrays var t = "<table>"; ???? // the firt row for column names for (var i = 0; i < ????; i++) { // for each row t += "<tr>"; for (var p in rows[i]) // for each property t += "<td>" + ???? + "</td>"; // the property value, not the property name t += "<td>"; t += "<button type='button' data-q-id='" + ???? + "'>Delete</button>"; // data-q-id: a custom data attribute for question Id t += "</td>"; t += "</tr>"; } t += "</table>"; ????; // display the table into <div> of id='tr2-6-result-pane' $(????).click(function() { // 'click' event registration for the above 'Delete' buttons, using the attribute selection // It should not be outside $.post(). alert($(this).attr(???)); // actually in TRUQA, need to send the 'DeleteQuestion' command. }); }); } </script>
  - Do you know how to delete a table row when a delete button in Trial 2.6 is clicked?
    - Send the DeleteQuestion command with the id stored in the delete button. How?
      $(this).attr("data-q-id")
    - Delete the row where the button is clicked. How?
      document.getElementById("myTable").deleteRow(this.parentNode.parentNode.rowIndex); or
      $(this).parent().parent().remove()
  - Trial 2.7: Let's search questions from TRUQA, and display it in a table.
    <div id='tr2-7-result-pane'>Result pane</div> <button type='button' id='tr2-7-search-questions'>Search Questions</button> <script> ???? // when the above button is clicked, call the next function function tr2_7_search_questions() { var url = "truqa_search_questions_ajax.php"; // test program for 'SearchQuestions' from TRUQA var query = {page: "MainPage", command: "SearchQuestions", "search-term":"How"}; // be careful with 'search-term': // jQuery post ????(???, ???, function(data) { var rows = ????(data); // convert a JSON string to an object // the object will be a linear array of associative arrays var t = "<table>"; for (var i = 0; i < ????; i++) { // for each row t += "<tr>"; for (????) // for each property t += "<td>" + ???? + "</td>"; // the property value, not the property name t += "</tr>"; } t += "</table>"; ????; // display the table into <div> of id='tr2-7-result-pane' }); } </script>
  - Trial 3: Let's write a JSON file.
     <div id='tr3-result'><div> <script> var file = ''; // Let's assume it is an actual text file. // Show the content in the file for the above course information // It should be a string containing an array of associative arrays. file = '??? \ {???, "Course title": "Web Site Design and Development"},\ {????},\ {????}\ ]'; var data = file; // Let's assume the content of the JSON file is sent from the server var obj = ???? (data); // convert the JSON string to an object. It will be a linear array. var count = 0; ???? // decide the number of associative arrays in the above array alert(count); // display the number ???? // form a table with the JSON data and display it in the above <div> </script>
  - Can you add table headers in the above Trial 3?
- How to convert a JSON object to a string?
  - JSON.stringify()
- How to convert a JSON string to PHP variables?
  - json_encode(): E.g., an array to a JSON string
  - json_decode(): E.g., a JSON encoded string to an ...

How to use JSON for TRUQA
- How to send the 'SearchQuestions' result to the client from the server?
```
// Model

function search_questions($search_term) 
{
    global $conn;
    
    $sql = "??? Id, Question, Date from Questions ??? Question like '%" . $search_term . "%'";
    $result = ???($conn, $sql);

    $str = '[';
    $first = true;
    while($row = ???($result)) {
        if ($first) $first = false;
        else $str .= ',';
        // $str .= '{"Question":"' . $row['Question'] . '", "Date":"' . $row['Date'] . '"}';  // {"Question" => "...", "Date" => "..."}
        $str .= ???($row);  // json_???
    }
    $str .= ']';
    return $str;
}

// Controller

echo search_questions($t);

/* or
* It is more desirable.
*/

// Model

function search_questions($search_term)
{
    global $conn;
    
    $sql = "??? Id, Question, Date from Questions ??? Question like '%" . $search_term . "%'";
    $result = ???($conn, $sql);
    
    $data = array();  // []
    $i = 0;
    while($row = ???($result))
        $data[$i++] = $row;  // $row: an associative array
    return $data;
}

// Controller

echo ???(search_questions($t));  // Convert the array to a JSON string and send
                                 // In general, you should not include MainPage or StartPage
```
- How to use the data sent from the server?
```
$('#button-search-questions').click(function() {
    var search_terms = ...;
    $.post('controller.php',
        { ???? },
        function(result) {  // retult: '[{...}, ...]'
            $('#result-pane').???('<h3>Searched Questions</h3>' + construct_table('List of questions', result));
    });
});

function construct_table(caption, data)  // data: '[{...}, ...]'
{
    // Convert the JSON string to an object
    var obj = ???.???(data);  
    // table tag
    var table = '<table>';
    // table caption
    table += ??? + caption + '</caption>';
    // table headers
    table += '<tr>';
    for (var p in obj[???][0])
        table += '<th>' + ??? + '</th>';  // property names
    table += '</tr>';
    // table data
    for (var i = 0; i < obj[???].???; i++) {
        table += '<tr>';
        for (var p in obj[???])
            table += '<td>' + ??? + '</td>';  // property values
        ????  // add some buttons.
        table += '</tr>';
    }
    // table end tag
    table += '</table>';

    return table;
}
```
- Trial 4: Let's try to send the list of questions in the JSON form.
  <script> // This variable will hold the value sent from the server script. // That is, the PHP code will be executed. var questions = '<?php // Model //$conn = mysqli_connect('localhost', ????); $conn = mysqli_connect('localhost', 'comp3540', 'comp3540136', 'COMP3540'); if (mysqli_connect_errno()) echo 'Failed to connect to MySQL: ' . mysqli_connect_error(); function get_userid($username) { global $conn; $sql = "select * from Users where (Username = '$username')"; $result = mysqli_query($conn, $sql); if (mysqli_num_rows($result) > 0) { $row = mysqli_fetch_assoc($result); return $row['Id']; // User ID } else return -1; } function list_questions($u) { global $conn; $uid = get_userid($u); if ($uid < 0) return []; $sql = "select * from Questions where (UserId = $uid)"; $result = mysqli_query($conn, $sql); $data = ????; // empty array $i = 0; while($row = ???($result)) // fetch a row $data[$i++] = ???; // include the row into the array return $data; // return the array of selected rows } // Controller $username = 'abc'; $data = list_questions($username); echo ???($data); // convert an array to a JSON string and send it back to the client. ?>'; // note that the single quote symbol is used. questions = ????(questions); // convert a JSON string to an object,i.e., a linear array of associative arrays ???? = tr4_make_table(questions); // display the table in the <div> function tr4_make_table(ta) { var str = '<table>'; ???? // row of table headers for (var i = 0; i < ta.???; i++) { str += '<tr>'; for (var p in ???) // for each ta[i] str += '<td>' + ??? + '</td>'; // the value, not property ???? // 'Delete' button str += '</tr>'; } str += '</table>'; return str; } </script> <div id='tr4-result'>Result here!</div>
- Trial 4.5: Can you add table headers in the above Trial 4?

Something with which we need to be careful
- In the case that there are double quouts in a string value
  - Trial 5: Let's try the next example. Be careful with double quotes in string values.
    <div id='tr5-result-1' style='border:solid 1px Blue; width:80%'>Result1 here!</div> <div id='tr5-result-2' style='border:solid 1px Blue; width:80%'>Result2 here!</div> <script> //var test = '{"name":"John","message":"Something is wrong with " !"}'; //test = JSON.parse(test); // It will make "uncaught Syntax Error: Unexpected string in JSON" //var test = '{"name":"John","message":"Something is wrong with \" !"}'; //test = JSON.parse(test); // It will make "uncaught Syntax Error: Unexpected string in JSON" var test = '{"name":"John","message":"Something is wrong with \\" !"}'; test = JSON.parse(test); // It is okay. $('#tr5-result-1').html(test.message); //test = '<?php $data = ['name'=>'John', 'message'=>'Something is wrong with " !']; // echo json_encode($data); // '{"name":"John","message":"Something is wrong with \" !"}' //?>'; //test = JSON.parse(test); // It will make "uncaught Syntax Error: Unexpected string in JSON" test = '<?php $data = ['name'=>'John', 'message'=>'Something is wrong with \" !']; echo json_encode($data); // '{"name":"John","message":"Something is wrong with \\" !"}' ?>'; test = JSON.parse(test); // It is okay. $('#tr5-result-2').html(test.message); </script>

Some review questions and learning outcomes
- Write JSON data.
- Use AJAX to retrieve JSON and text data.

5.7 Lightweight data-interchange format - JSON